package com.istudy.dao.impl;

import com.istudy.dao.BaseDao;
import com.istudy.dao.interfaces.CommentDao;
import com.istudy.entity.Comment;
import com.istudy.entity.User;
import com.istudy.entity.Video;
import com.istudy.utils.Page;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class CommentDaoImpl implements CommentDao {

public BaseDao baseDao=new BaseDao();
    @Override
    public int insertComment(Comment comment  ) {
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO comment ");
        sb.append(" (comment_user_id,comment_video_id,comment_data_time,comment_content)");
        sb.append(" VALUES (?,?,?,?)");
        List<Object> list=new ArrayList<>();
        list.add(comment.getCommentUserId());
        list.add((comment.getCommentVideoId())%8);
        list.add(comment.getCommentDataTime());
        list.add(comment.getCommentContent());
        int count = baseDao.executeUpdate(sb.toString(),list.toArray());
        return count;
    }

    @Override
    public int deleteComment(int id) {
        String sql = "delete from comment where comment_id =?";
        List<Object> objects=new ArrayList<>();
        objects.add(id);
        int count = baseDao.executeUpdate(sql,objects.toArray());
        return count;
    }

    @Override
    public int getCount(User user) {
        String sql="SELECT COUNT(1) FROM comment WHERE `comment_user_id`=?";
        List<Object> o=new ArrayList<>();
        o.add(user.getUserId());
        BaseDao base=new BaseDao();
        ResultSet rs= base.executeQuery(sql,o.toArray());
        int count=0;
        try {
            if(rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 根据视频查询总记录数
     *
     * @param video
     * @return 总记录数
     */
    @Override
    public int getCount(Video video) {
        String sql="SELECT COUNT(1) FROM comment where comment_video_id=?";
        List<Object> o=new ArrayList<>();
        o.add((video.getVideoId())%8);
        BaseDao base=new BaseDao();
        ResultSet rs= base.executeQuery(sql,o.toArray());
        int count=0;
        try {
            if(rs.next()){
                count=rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }


    @Override
    public List<Comment> queryCommentByUser(User user,Page page) {
        String sql = "SELECT a.`comment_id`, a.`comment_user_id`,b.`user_name` " +
                " ,a.`comment_video_id`,c.`video_name` ,a.`comment_data_time`" +
                ",a.`comment_content` FROM `comment` AS a JOIN `user` AS b ," +
                "`video` AS c WHERE a.`comment_user_id`=b.`user_id` AND" +
                " a.`comment_video_id`=c.`video_id` AND a.`comment_user_id`" +
                "=? ORDER BY a.`comment_data_time` DESC LIMIT ?,5";
        List<Object> objects=new ArrayList<>();
        objects.add(user.getUserId());
        objects.add(page.getPageSize()*(page.getPageNo()-1));
        ResultSet set = baseDao.executeQuery(sql, objects.toArray());
        List<Comment> list = new ArrayList<>();
        try {
            while(set.next()){
                Comment comment = tableToClass(set);
                list.add(comment);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Comment> queryCommentByVideo(Video video,Page page) {
        String sql = "SELECT\n" +
                "\tcomment.*, \n" +
                "\tuser.user_name AS user_name, \n" +
                "\tvideo.video_name AS video_name\n" +
                "FROM\n" +
                "\t`comment` AS `comment`\n" +
                "\tLEFT JOIN\n" +
                "\t`user` AS `user`\n" +
                "\tON \n" +
                "\t\tcomment.comment_user_id = user.user_id\n" +
                "\tLEFT JOIN\n" +
                "\tvideo AS video\n" +
                "\tON \n" +
                "\t\tcomment.comment_video_id = video.video_id\n" +
                "WHERE\n" +
                "\tcomment.comment_video_id = ? ORDER BY comment.`comment_data_time` DESC  LIMIT ?,5";
        List<Object> objects=new ArrayList<>();
        objects.add((video.getVideoId())%8);
        objects.add(page.getPageSize()*(page.getPageNo()-1));
        ResultSet set = baseDao.executeQuery(sql, objects.toArray());
        List<Comment> list = new ArrayList<>();
        try {
            while(set.next()){
                Comment comment = tableToClass(set);
                list.add(comment);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


    @Override
    public int updataComment(Comment comment ) {
        return 0;
    }

    public Comment tableToClass(ResultSet rs) throws Exception {
        Comment comment = new Comment();
        comment.setCommentUserName(rs.getString("user_name"));
        comment.setCommentVideoName(rs.getString("video_name"));
       comment.setCommentUserId(rs.getInt("comment_user_id"));
       comment.setCommentVideoId(rs.getInt("comment_Video_Id"));
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:SS");
        Date date = (Date)rs.getObject("comment_Data_Time");
       comment.setCommentDataTime(simpleDateFormat.format(date));
       comment.setCommentContent(rs.getString("comment_Content"));
       comment.setCommentId(rs.getInt("comment_Id"));
        return comment;
    }

}
